USE BigDB
-- This file demonstrates different approaches to implement chunking
-- when the clustered index is not a single-column unique key.
-- Performance data is at the end of the script.
go
-- This is the naïve approach: we ignore that the leading column
-- in the clustered index is unique. This means that chunks may 
-- be bigger than the intended chunksize, but this is not an issue
-- if the number of excess rows is moderate. This is the method
-- you want to use as much as possible.
CREATE OR ALTER PROCEDURE UpdatePrice_simple @chunksize int AS
   DECLARE @minID int,
           @maxID int

   SELECT @minID = MIN(OrderID) FROM BigDetails

   WHILE @minID IS NOT NULL
   BEGIN
      SELECT @maxID = MAX(OrderID)
      FROM   (SELECT TOP(@chunksize) OrderID
              FROM   BigDetails
              WHERE  OrderID >= @minID
              ORDER  BY OrderID ASC) AS B

      UPDATE BigDetails
      SET    UnitPrice = UnitPrice * 1.2
      WHERE  OrderID BETWEEN @minID AND @maxID
      OPTION (RECOMPILE)

      SELECT @minID = MIN(OrderID) FROM BigDetails WHERE OrderID > @maxID
   END
go
-------------------------------------------------------------------
-- Say that there are too many products per order for it to be 
-- defensible to loop only the over the OrderID. So we add variables
-- for the product ID, can't be very difficult, can it? Turns out 
-- that it is not that simple, watch the below.
CREATE OR ALTER PROCEDURE UpdatePrice_double @chunksize int AS
   DECLARE @minOrderID    int,
           @minProductID  int,
           @maxOrderID    int,
           @maxProductID  int,
           @stopOrderID   int,
           @stopProductID int,
           @gameover      bit = 0
        
    -- Get start and end points.
    SELECT TOP (1) @minOrderID = OrderID, @minProductID = ProductID
    FROM   BigDetails
    ORDER  BY OrderID ASC, ProductID ASC

    SELECT TOP (1) @stopOrderID = OrderID, @stopProductID = ProductID
    FROM   BigDetails
    ORDER  BY OrderID DESC, ProductID DESC

    WHILE @gameover = 0
    BEGIN
       -- Init variables before getting the end of the interval.
       SELECT @maxOrderID = NULL, @maxProductID = NULL

       -- Get the end of the interval, but for the last chunk
       -- we will not get a hit, since rowno will not reach
       -- @chunkno.
       ; WITH numbering AS (
            SELECT TOP(@chunksize) OrderID, ProductID,
                   row_number() OVER (ORDER BY OrderID, ProductID) AS rowno
            FROM   BigDetails
            WHERE  OrderID = @minOrderID AND ProductID >= @minProductID OR
                   OrderID > @minOrderID
            ORDER  BY OrderID, ProductID
       )
       SELECT @maxOrderID = OrderID, @maxProductID = ProductID
       FROM   numbering
       WHERE  rowno = @chunksize

       IF @maxOrderID IS NULL
       BEGIN
          -- Variable was not assigned. This is the last chunk.
          SELECT @gameover = 1, 
                 @maxOrderID   = @stopOrderID, 
                 @maxProductID = @stopProductID
       END

       -- There are several cases to consider. Normally(?) a chunk starts
       -- in the middle of an order and ends in the middle of another 
       -- one with a couple of complete orders in between. But if an
       -- order has many products, the chunk may be within a single order, 
       -- which needs to be handled separately.
       IF @maxOrderID > @minOrderID
       BEGIN
          -- Love that WHERE conditon? Yuk!
          UPDATE BigDetails
          SET    UnitPrice = UnitPrice * 1.2
          WHERE  OrderID = @minOrderID AND ProductID >= @minProductID OR
                 OrderID > @minOrderID AND OrderID < @maxOrderID OR
                 OrderID = @maxOrderID AND ProductID <= @maxProductID
          OPTION (RECOMPILE)
       END
       ELSE
       BEGIN
          UPDATE BigDetails
          SET    UnitPrice = UnitPrice * 1.2
          WHERE  OrderID = @minOrderID 
            AND  ProductID BETWEEN @minProductID AND @maxProductID
          OPTION (RECOMPILE)
       END

       IF @gameover = 0
       BEGIN
          -- Get the starting point for the next chunk.
          SELECT TOP (1) @minOrderID   = OrderID, 
                         @minProductID = ProductID
          FROM   BigDetails
          WHERE  OrderID = @maxOrderID AND ProductID > @maxProductID OR
                 OrderID > @maxOrderID
          ORDER  BY OrderID, ProductID
      END
   END
go
------------------------------------------------------------------
-- Not only is that WHERE condition for the UPDATE frightening - it's
-- also inefficient, because the optimizer thinks it has to do more
-- work than it has to. We need to split it up in three statements 
-- (which we wrap in in a transaction to reduce overhead). Performance 
-- is now reasonbly close to the naïve solution for bigger chunk sizes, 
-- but still 40% longer time for 1000 rows - and it is at smaller chunk 
-- size, you may want to do this in the first place. (However, this is 
-- because there are now three statements with OPTION (RECOMPILE). 
-- Wrapping in dynamic SQL may give better results. See perf data below.
CREATE OR ALTER PROCEDURE UpdatePrice_triple @chunksize int AS
   DECLARE @minOrderID    int,
           @minProductID  int,
           @maxOrderID    int,
           @maxProductID  int,
           @stopOrderID   int,
           @stopProductID int,
           @gameover      bit = 0
        
    -- Get start and end points.
    SELECT TOP (1) @minOrderID = OrderID, @minProductID = ProductID
    FROM   BigDetails
    ORDER  BY OrderID ASC, ProductID ASC

    SELECT TOP (1) @stopOrderID = OrderID, @stopProductID = ProductID
    FROM   BigDetails
    ORDER  BY OrderID DESC, ProductID DESC

    WHILE @gameover = 0
    BEGIN
       -- Init variables before getting the end of the interval.
       SELECT @maxOrderID = NULL, @maxProductID = NULL

       -- Get the end of the interval, but for the last chunk
       -- we will not get a hit, since rowno will not reach
       -- @chunkno.
       ; WITH numbering AS (
            SELECT TOP(@chunksize) OrderID, ProductID,
                   row_number() OVER (ORDER BY OrderID, ProductID) AS rowno
            FROM   BigDetails
            WHERE  OrderID = @minOrderID AND ProductID >= @minProductID OR
                   OrderID > @minOrderID
            ORDER  BY OrderID, ProductID
       )
       SELECT @maxOrderID = OrderID, @maxProductID = ProductID
       FROM   numbering
       WHERE  rowno = @chunksize

       IF @maxOrderID IS NULL
       BEGIN
          -- Variable was not assigned. This is the last chunk.
          SELECT @gameover = 1, 
                 @maxOrderID   = @stopOrderID, 
                 @maxProductID = @stopProductID
       END

       IF @maxOrderID > @minOrderID
       BEGIN
          BEGIN TRANSACTION
       
          UPDATE BigDetails
          SET    UnitPrice = UnitPrice * 1.2
          WHERE  OrderID = @minOrderID AND ProductID >= @minProductID
          OPTION (RECOMPILE)

          UPDATE BigDetails
          SET    UnitPrice = UnitPrice * 1.2
          WHERE  OrderID > @minOrderID AND OrderID < @maxOrderID
          OPTION (RECOMPILE)

          UPDATE BigDetails
          SET    UnitPrice = UnitPrice * 1.2
          WHERE  OrderID = @maxOrderID AND ProductID <= @maxProductID
          OPTION (RECOMPILE)

          COMMIT TRANSACTION
       END
       ELSE
       BEGIN
          UPDATE BigDetails
          SET    UnitPrice = UnitPrice * 1.2
          WHERE  OrderID = @minOrderID 
            AND  ProductID BETWEEN @minProductID AND @maxProductID
          OPTION (RECOMPILE)
       END

       IF @gameover = 0
       BEGIN
          -- Get the starting point for the next chunk.
          SELECT TOP (1) @minOrderID   = OrderID, 
                         @minProductID = ProductID
          FROM   BigDetails
          WHERE  OrderID = @maxOrderID AND ProductID > @maxProductID OR
                 OrderID > @maxOrderID
          ORDER  BY OrderID, ProductID
      END
   END
go
----------------------------------------------------------------
-- Here is a solution which is less efficient but far less complex
-- and therefore more palatable. This solution reads all key values 
-- into a temp table and divides them into chunks and then loops 
-- over the temp table, updating the table one chunk at a time. 
-- Care is taken so that a chunk includes adjacent rows in BigDetails 
-- to make the join efficient.
CREATE OR ALTER PROCEDURE UpdatePrice_temptbl @chunksize int AS
   DECLARE @chunkno int

   -- The #chunks table. Note the clustered index on ChunkNo
   -- followed by the keys in BigDetails. While we could define
   -- (OrderID, ProductID) as a key in itself, that only takes
   -- time to do.
   CREATE TABLE #chunks (
       OrderID   int  NOT NULL,
       ProductID int  NOT NULL,
       ChunkNo   int  NOT NULL,
       INDEX Clu UNIQUE CLUSTERED (ChunkNo, OrderID, ProductID)
   )

   -- Fill up the temp table. We use row_number to define the chunks.
   -- Note that we order by the key values, so that a chunk has a set
   -- of adjacent rows in the target table.
   INSERT #chunks (OrderID, ProductID, ChunkNo)
      SELECT OrderID, ProductID, 
             (row_number() OVER(ORDER BY OrderID, ProductID) - 1) / 
                    @chunksize
      FROM  BigDetails

   -- Then loop over all the chunks.
   DECLARE @chunkcur CURSOR
   SET @chunkcur = CURSOR STATIC FOR
      SELECT DISTINCT ChunkNo FROM #chunks

   OPEN @chunkcur

   WHILE 1 = 1
   BEGIN
      FETCH @chunkcur INTO @chunkno
      IF @@fetch_status <> 0
         BREAK

      -- Update the rows in this chunk.
      UPDATE BigDetails
      SET    UnitPrice = UnitPrice * 1.2
      FROM   BigDetails BD
      WHERE  EXISTS (SELECT *
                     FROM   #chunks b
                     WHERE  b.OrderID   = BD.OrderID
                       AND  b.ProductID = BD.ProductID
                       AND  b.ChunkNo   = @chunkno)
   END
go
---------------------------------------------------------------------
-- Here is a solution that avoids an initial scan the entire table to 
-- get the chunks, but instead fills a temp table with one chunk at a 
-- time as it moves on. When I tested, it was faster than the temptbl
-- solution for some chunk sizes, but slower for others.
CREATE OR ALTER PROCEDURE UpdatePrice_smalltemp @chunksize int AS
   DECLARE @CurOrderID    int,
           @LastProductID int,
           @rowcnt        int = @chunksize

   -- No chunk number in this table.
   CREATE TABLE #thischunk (OrderID   int NOT NULL,
                            ProductID int NOT NULL,
                            PRIMARY KEY (OrderID, ProductID)
   )

   SELECT @CurOrderID = MIN(OrderID) - 1
   FROM   BigDetails

   WHILE @rowcnt = @chunksize
   BEGIN
      -- Make sure table is empty. 
      TRUNCATE TABLE #thischunk

      -- Load the table with @chunksize rows, starting where ended
      -- last time.
      INSERT #thischunk(OrderID, ProductID)
         SELECT TOP (@chunksize) OrderID, ProductID
         FROM   BigDetails
         WHERE  OrderID = @CurOrderID AND ProductID > @LastProductID OR
                OrderID > @CurOrderID
         ORDER  BY OrderID, ProductID

      -- Track the number of rows found. If less than @chunksize
      -- rows, this is the last chunk.
      SELECT @rowcnt = @@rowcount

      -- Update for this chunk.
      UPDATE BigDetails
      SET    UnitPrice = UnitPrice * 1.2
      FROM   BigDetails BD
      WHERE  EXISTS (SELECT *
                     FROM   #thischunk t
                     WHERE  t.OrderID   = BD.OrderID
                       AND  t.ProductID = BD.ProductID)

      -- Find out which is the last row in this chunk, so that 
      -- we know where to start the next.
      SELECT TOP 1 @CurOrderID = OrderID, @LastProductID = ProductID
      FROM   #thischunk
      ORDER  BY OrderID DESC, ProductID DESC
   END
go
-------------------------------------------------------------------
-- This solution is almost identical to UpdatePrice_temptbl. The only 
-- difference is how the chunk numbers are computed. By not sorting
-- the key values, they can be read from the NC index on ProductID
-- which makes that part faster. But with all chunks having key values 
-- scattered all over BigDetails, this solution is seriously slower
-- than the previous one.
CREATE OR ALTER PROCEDURE UpdatePrice_temptbl2 @chunksize int AS
   DECLARE @chunkno int

   -- Now only with a clustered index on ChunkNo.
   CREATE TABLE #chunks (
       OrderID   int  NOT NULL,
       ProductID int  NOT NULL,
       ChunkNo   int  NOT NULL,
       INDEX chunkno_ix CLUSTERED (ChunkNo)
   )

   -- Fill up the temp table. We decline to order the rows in any way.
   INSERT #chunks (OrderID, ProductID, ChunkNo)
      SELECT OrderID, ProductID, 
             (row_number() OVER(ORDER BY (SELECT 1)) - 1) / @chunksize
      FROM  BigDetails

   -- Then loop over all the chunks.
   DECLARE @chunkcur CURSOR
   SET @chunkcur = CURSOR STATIC FOR
      SELECT DISTINCT ChunkNo FROM #chunks

   OPEN @chunkcur

   WHILE 1 = 1
   BEGIN
      FETCH @chunkcur INTO @chunkno
      IF @@fetch_status <> 0
         BREAK

      -- Update the rows in this chunk. Note that they key values
      -- now are all over the place in the table.
      UPDATE BigDetails
      SET    UnitPrice = UnitPrice * 1.2
      FROM   BigDetails BD
      WHERE  EXISTS (SELECT *
                     FROM   #chunks b
                     WHERE  b.OrderID   = BD.OrderID
                       AND  b.ProductID = BD.ProductID
                       AND  b.ChunkNo   = @chunkno)
   END
go
------------------------------------------------------------------
-- Performance data for the above (from my tests in September 2020 on SQL 2019.)
-- chunksize  simple   double  triple  temptbl  tempsimple  temptbl2
-- 1000           36      115      46      114         117       324 
-- 5000           30       84      33      107          92       313 
-- 30000          30       76      32       87          67       212 
-- 200000         29       76      29      123          64       239 
-- 1000000        29       72      30       93          64       144 
-- 6000000        32       71      30       81          78        92 

-- Below are the same procedures for dynamic SQL. Here is their
-- performance data.
-- chunksize  simple   double  triple  temptbl  tempsimple  temptbl2
-- 1000           45      201      56      114         162       209 
-- 5000           42      188      42      108         152       164 
-- 30000          33      167      31       87         137       206 
-- 200000         29       69      30      123         138       138 
-- 1000000        30       68      28       93         143       150 
-- 6000000        31       38      29       79         156        95 
go
CREATE OR ALTER PROCEDURE UpdatePrice_simple_dynsql @chunksize int AS
   DECLARE @minID int,
           @maxID int

   SELECT @minID = MIN(OrderID) FROM BigDetails

   WHILE @minID IS NOT NULL
   BEGIN
      SELECT @maxID = MAX(OrderID)
      FROM   (SELECT TOP(@chunksize) OrderID
              FROM   BigDetails
              WHERE  OrderID >= @minID
              ORDER  BY OrderID ASC) AS B

      EXEC sp_executesql N'UPDATE BigDetails
                           SET    UnitPrice = UnitPrice * 1.2
                           WHERE  OrderID BETWEEN @minID AND @maxID',
                           N'@minID int, @maxID int', @minID, @maxID

      SELECT @minID = MIN(OrderID) FROM BigDetails WHERE OrderID > @maxID
   END
GO

CREATE OR ALTER PROCEDURE UpdatePrice_double_dynsql @chunksize int AS
   DECLARE @FirstOrderID   int,
           @LastOrderID    int,
           @StopOrderID    int,
           @FirstProductID int,
           @LastProductID  int,
           @StopProductID  int,
           @gameover       bit = 0
        
    SELECT TOP (1) @FirstOrderID = OrderID, @FirstProductID = ProductID
    FROM   BigDetails
    ORDER  BY OrderID ASC, ProductID ASC

    SELECT TOP (1) @StopOrderID = OrderID, @StopProductID = ProductID
    FROM   BigDetails
    ORDER  BY OrderID DESC, ProductID DESC

    WHILE @gameover = 0
    BEGIN
       SELECT @LastOrderID = NULL, @LastProductID = NULL

       ; WITH numbering AS (
            SELECT TOP(@chunksize) OrderID, ProductID,
                   row_number() OVER (ORDER BY OrderID, ProductID) AS rowno
            FROM   BigDetails
            WHERE  OrderID = @FirstOrderID AND ProductID >= @FirstProductID OR
                   OrderID > @FirstOrderID
            ORDER  BY OrderID, ProductID
       )
       SELECT @LastOrderID = OrderID, @LastProductID = ProductID
       FROM   numbering
       WHERE  rowno = @chunksize

       IF @LastOrderID IS NULL
       BEGIN
          SELECT @gameover = 1, 
                 @LastOrderID = @StopOrderID, @LastProductID = @StopProductID
       END

       IF @LastOrderID > @FirstOrderID
       BEGIN
          EXEC sp_executesql 
               N'UPDATE BigDetails
                 SET    UnitPrice = UnitPrice * 1.2
                 WHERE  OrderID = @FirstOrderID AND ProductID >= @FirstProductID OR
                        OrderID > @FirstOrderID AND OrderID < @LastOrderID OR
                        OrderID = @LastOrderID AND ProductID <= @LastProductID',
              N'@FirstOrderID int, @FirstProductID int, @LastOrderID int, @LastProductID int',
              @FirstOrderID, @FirstProductID, @LastOrderID, @LastProductID
       END
       ELSE
       BEGIN
          EXEC sp_executesql 
               N'UPDATE BigDetails
                 SET    UnitPrice = UnitPrice * 1.2
                 WHERE  OrderID = @FirstOrderID 
                   AND  ProductID BETWEEN @FirstProductID AND @LastProductID',
              N'@FirstOrderID int, @FirstProductID int, @LastProductID int',
              @FirstOrderID, @FirstProductID, @LastProductID
       END

       IF @gameover = 0
       BEGIN
          SELECT @FirstOrderID = NULL, @FirstProductID = NULL

          SELECT TOP (1) @FirstOrderID = OrderID, @FirstProductID = ProductID
          FROM   BigDetails
          WHERE  OrderID = @LastOrderID AND ProductID > @LastProductID OR
                 OrderID > @LastOrderID
          ORDER  BY OrderID, ProductID
      END
   END
GO
CREATE OR ALTER PROCEDURE UpdatePrice_triple_dynsql @chunksize int AS
   DECLARE @FirstOrderID   int,
           @LastOrderID    int,
           @StopOrderID    int,
           @FirstProductID int,
           @LastProductID  int,
           @StopProductID  int,
           @gameover       bit = 0
        
    SELECT TOP (1) @FirstOrderID = OrderID, @FirstProductID = ProductID
    FROM   BigDetails
    ORDER  BY OrderID ASC, ProductID ASC

    SELECT TOP (1) @StopOrderID = OrderID, @StopProductID = ProductID
    FROM   BigDetails
    ORDER  BY OrderID DESC, ProductID DESC

    WHILE @gameover = 0
    BEGIN
       SELECT @LastOrderID = NULL, @LastProductID = NULL

       ; WITH numbering AS (
            SELECT TOP(@chunksize) OrderID, ProductID,
                   row_number() OVER (ORDER BY OrderID, ProductID) AS rowno
            FROM   BigDetails
            WHERE  OrderID = @FirstOrderID AND ProductID >= @FirstProductID OR
                   OrderID > @FirstOrderID
            ORDER  BY OrderID, ProductID
       )
       SELECT @LastOrderID = OrderID, @LastProductID = ProductID
       FROM   numbering
       WHERE  rowno = @chunksize

       IF @LastOrderID IS NULL
       BEGIN
          SELECT @gameover = 1, 
                 @LastOrderID = @StopOrderID, @LastProductID = @StopProductID
       END

       IF @LastOrderID > @FirstOrderID
       BEGIN
          BEGIN TRANSACTION
       
          EXEC sp_executesql 
               N'UPDATE BigDetails
                 SET    UnitPrice = UnitPrice * 1.2
                 WHERE  OrderID = @FirstOrderID AND ProductID >= @FirstProductID
                 
                 UPDATE BigDetails
                 SET    UnitPrice = UnitPrice * 1.2
                 WHERE  OrderID > @FirstOrderID AND OrderID < @LastOrderID

                 UPDATE BigDetails
                 SET    UnitPrice = UnitPrice * 1.2
                 WHERE  OrderID = @LastOrderID AND ProductID <= @LastProductID',
              N'@FirstOrderID int, @FirstProductID int, @LastOrderID int, @LastProductID int',
                @FirstOrderID, @FirstProductID, @LastOrderID, @LastProductID

          COMMIT TRANSACTION
       END
       ELSE
       BEGIN
          EXEC sp_executesql 
               N'UPDATE BigDetails
                 SET    UnitPrice = UnitPrice * 1.2
                 WHERE  OrderID = @FirstOrderID 
                   AND  ProductID BETWEEN @FirstProductID AND @LastProductID',
               N'@FirstProductID int, @LastProductID int', @FirstProductID, @LastProductID

       END

       IF @gameover = 0
       BEGIN
          SELECT @FirstOrderID = NULL, @FirstProductID = NULL

          SELECT TOP (1) @FirstOrderID = OrderID, @FirstProductID = ProductID
          FROM   BigDetails
          WHERE  OrderID = @LastOrderID AND ProductID > @LastProductID OR
                 OrderID > @LastOrderID
          ORDER BY OrderID, ProductID
      END
   END
GO
CREATE OR ALTER PROCEDURE UpdatePrice_temptbl_dynsql @chunksize int AS
   DECLARE @chunkno int

   CREATE TABLE #chunks (OrderID   int  NOT NULL,
                          ProductID int  NOT NULL,
                          ChunkNo   int  NOT NULL,
                          PRIMARY KEY (ChunkNo, OrderID, ProductID)
   )

   INSERT #chunks (OrderID, ProductID, ChunkNo)
      SELECT OrderID, ProductID, 
             (row_number() OVER(ORDER BY OrderID, ProductID) - 1) / @chunksize
      FROM  BigDetails

   DECLARE @chunkcur CURSOR
   SET @chunkcur = CURSOR STATIC FOR
      SELECT DISTINCT ChunkNo FROM #chunks

   OPEN @chunkcur

   WHILE 1 = 1
   BEGIN
      FETCH @chunkcur INTO @chunkno
      IF @@fetch_status <> 0
         BREAK

      EXEC sp_executesql
           N'UPDATE BigDetails
             SET    UnitPrice = UnitPrice * 1.2
             FROM   BigDetails BD
             WHERE  EXISTS (SELECT *
                            FROM   #chunks b
                            WHERE  b.OrderID   = BD.OrderID
                              AND  b.ProductID = BD.ProductID
                              AND  b.ChunkNo   = @chunkno)', N'@chunkno int', @chunkno
   END
GO
CREATE OR ALTER PROCEDURE UpdatePrice_smalltemp_dynsql @chunksize int AS
   DECLARE @LastOrderID   int,
           @LastProductID int,
           @rowc          int = @chunksize

   CREATE TABLE #thischunk (OrderID   int NOT NULL,
                            ProductID int NOT NULL,
                            PRIMARY KEY (OrderID, ProductID)
   )

   SELECT @LastOrderID = MIN(OrderID) - 1
   FROM   BigDetails

   WHILE @rowc = @chunksize
   BEGIN
      TRUNCATE TABLE #thischunk

      INSERT #thischunk(OrderID, ProductID)
         EXEC sp_executesql N'SELECT TOP (@chunksize) OrderID, ProductID
                              FROM   BigDetails
                              WHERE  OrderID = @LastOrderID AND ProductID > @LastProductID OR
                                     OrderID > @LastOrderID
                              ORDER  BY OrderID, ProductID',
                            N'@chunksize int, @LastOrderID int, @LastProductID int',
                            @chunksize, @LastOrderID, @LastProductID
      SELECT @rowc = @@rowcount

      UPDATE BigDetails
      SET    UnitPrice = UnitPrice * 1.2
      FROM   BigDetails BD
      WHERE  EXISTS (SELECT *
                     FROM   #thischunk t
                     WHERE  t.OrderID   = BD.OrderID
                       AND  t.ProductID = BD.ProductID)

      SELECT TOP (1) @LastOrderID = OrderID, @LastProductID = ProductID
      FROM   #thischunk
      ORDER  BY OrderID DESC, ProductID DESC
   END
GO



